from datetime import datetime
from sqlalchemy.orm import Session
from sqlalchemy import func
from typing import List, Optional
from app.models.bill import Bill
from app.schemas.bill import BillCreate, BillUpdate

def get_bill(db: Session, bill_id: int) -> Optional[Bill]:
    return db.query(Bill).filter(Bill.id == bill_id).first()

def get_bills(
    db: Session, 
    user_id: int, 
    skip: int = 0, 
    limit: int = 20,
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None
) -> List[Bill]:
    query = db.query(Bill).filter(Bill.user_id == user_id)
    
    if start_date:
        query = query.filter(Bill.date >= start_date)
    if end_date:
        query = query.filter(Bill.date <= end_date)
        
    return query.order_by(Bill.date.desc()).offset(skip).limit(limit).all()

def get_bills_count(
    db: Session, 
    user_id: int,
    start_date: Optional[datetime] = None,
    end_date: Optional[datetime] = None
) -> int:
    query = db.query(func.count(Bill.id)).filter(Bill.user_id == user_id)
    
    if start_date:
        query = query.filter(Bill.date >= start_date)
    if end_date:
        query = query.filter(Bill.date <= end_date)
        
    return query.scalar()

def create_bill(db: Session, bill: BillCreate, user_id: int) -> Bill:
    db_bill = Bill(
        user_id=user_id,
        **bill.model_dump()
    )
    db.add(db_bill)
    db.commit()
    db.refresh(db_bill)
    return db_bill

def update_bill(db: Session, bill_id: int, bill: BillUpdate, user_id: int) -> Optional[Bill]:
    db_bill = get_bill(db, bill_id)
    if db_bill:
        for key, value in bill.model_dump(exclude_unset=True).items():
            setattr(db_bill, key, value)
        db.commit()
        db.refresh(db_bill)
    return db_bill

def delete_bill(db: Session, bill_id: int, user_id: int) -> bool:
    db_bill = get_bill(db, bill_id)
    if db_bill:
        db.delete(db_bill)
        db.commit()
        return True
    return False

def get_bill_stats(
    db: Session,
    user_id: int,
    start_date: datetime,
    end_date: datetime
):
    # 获取总览数据
    overview = db.query(
        Bill.type,
        func.sum(Bill.amount).label('total')
    ).filter(
        Bill.user_id == user_id,
        Bill.date.between(start_date, end_date)
    ).group_by(Bill.type).all()
    
    # 获取分类统计
    categories = db.query(
        Bill.type,
        Bill.category,
        func.sum(Bill.amount).label('total')
    ).filter(
        Bill.user_id == user_id,
        Bill.date.between(start_date, end_date)
    ).group_by(Bill.type, Bill.category).all()
    
    # 处理数据
    overview_data = [{'type': t, 'total': float(total)} for t, total in overview]
    
    # 按类型分组处理分类数据
    category_stats = {}
    for bill_type, category, total in categories:
        if bill_type not in category_stats:
            category_stats[bill_type] = []
            
        type_total = next(item['total'] for item in overview_data if item['type'] == bill_type)
        percent = (total / type_total * 100) if type_total > 0 else 0
        
        category_stats[bill_type].append({
            'category': category,
            'total': float(total),
            'percent': round(percent, 2)
        })
    
    return {
        'overview': overview_data,
        'categories': category_stats
    } 